package com.feib.stms.dao.jdbc;


import java.sql.Types;
import java.util.Date;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;
import org.springframework.stereotype.Repository;

import com.feib.stms.dao.MailQueueDao;
import com.feib.stms.dao.jdbc.rowmapper.MailQueueRowMapper;
import com.feib.stms.model.MailQueue;

@Repository("mailQueueDao")
public class MailQueueJdbcDao extends BaseJdbcDao implements MailQueueDao {

	private OracleSequenceMaxValueIncrementer sequence;

	private SimpleJdbcInsert mailQueueJdbcInsert;


	@Override
	protected void initDao() throws Exception {
		super.initDao();
		this.sequence = new  OracleSequenceMaxValueIncrementer(this.getDataSource(), "MAIL_QUEUE_PK_SEQ");

		this.mailQueueJdbcInsert = new  SimpleJdbcInsert(this.getDataSource());
		this.mailQueueJdbcInsert.withTableName("MAIL_QUEUE")
		.usingColumns("MAIL_QUEUE_PK", "MAIL_SUBJECT", "MAIL_FROM" ,"MAIL_TO" ,"MAIL_RETURN_RECEIPT_TO"  
				,"MAIL_CONTENT" ,"INLINE_RESOURCE", "IN_QUEUE_DATE", "OUT_QUEUE_DATE", "MAIL_STATUS" 
				,"CREATED_BY" ,"CREATED_DATE" ,"UPDATED_BY" ,"UPDATED_DATE" );

	}

	public MailQueue findUnSendMail() {

		StringBuffer sql = new StringBuffer();
		sql.append("select * from Mail_Queue ");
		sql.append("where mailStatus = 0 ");
		try{
			return DataAccessUtils.singleResult(this.getSimpleJdbcTemplate().query(sql.toString(), new MailQueueRowMapper()));
		}catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	public int insert(MailQueue mailQueue) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		Long mailQueuePk = new Long(this.sequence.nextLongValue());
		parameters.addValue("MAIL_QUEUE_PK",  mailQueuePk, Types.NUMERIC);
		parameters.addValue("MAIL_SUBJECT",  mailQueue.getMailSubject(), Types.VARCHAR);
		parameters.addValue("MAIL_FROM",  mailQueue.getMailFrom(), Types.VARCHAR);
		parameters.addValue("MAIL_TO",  mailQueue.getMailTo(), Types.VARCHAR);
		parameters.addValue("MAIL_RETURN_RECEIPT_TO",  mailQueue.getMailReturnReceiptTo(), Types.VARCHAR);
		parameters.addValue("MAIL_CONTENT",  mailQueue.getMailContent(), Types.VARCHAR);
		parameters.addValue("INLINE_RESOURCE", mailQueue.getInlineResource()==null?null:mailQueue.getInlineResource(), Types.VARCHAR);
		parameters.addValue("IN_QUEUE_DATE",  new Date(), Types.TIMESTAMP);
		parameters.addValue("MAIL_STATUS",  mailQueue.getMailStatus(), Types.NUMERIC);
		parameters.addValue("CREATED_BY",  mailQueue.getCreatedBy(), Types.VARCHAR);
		parameters.addValue("CREATED_DATE", new Date(), Types.TIMESTAMP);
	
		int insertCounts = this.mailQueueJdbcInsert.execute(parameters);
		if (insertCounts > 0)
			mailQueue.setMailQueuePk(mailQueuePk);
		return insertCounts;

	}

	public MailQueue queryByPk(Long mailQueuePk) {

		MapSqlParameterSource parameters = new MapSqlParameterSource();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from Mail_Queue WHERE MAIL_QUEUE_PK = :MAIL_QUEUE_PK");
		parameters.addValue("MAIL_QUEUE_PK",  mailQueuePk, Types.NUMERIC);		
		try{
			return DataAccessUtils.singleResult(this.getSimpleJdbcTemplate().query(sql.toString(), new MailQueueRowMapper(),parameters));
		}catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	public int updateMailStatus(MailQueue mailQueue) {

		MapSqlParameterSource parameters = new MapSqlParameterSource();

		StringBuffer sql = new StringBuffer();
		sql.append(" update Mail_Queue set mail_Status = :mailStatus ");
		sql.append(" where mail_Queue_Pk = :mailQueuePk ");
		parameters.addValue("mailStatus", mailQueue.getMailStatus(), Types.NUMERIC);
		parameters.addValue("mailQueuePk", mailQueue.getMailQueuePk(), Types.NUMERIC);
		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);

	}

}
